﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Common;
using System.Data.SqlClient;

namespace WorkDAL.LookUp
{
    public class Lookup
    {
        WorkerDataContext listData = new WorkerDataContext();

        public static Lookup Instance()
        {
            return new Lookup();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="columnList"></param>
        /// <param name="aliasList"></param>
        /// <param name="tableName"></param>
        /// <param name="columnKey"></param>
        /// <param name="valueKey"></param>
        /// <returns></returns>
        public DataSet LookupDs(string columnList, string aliasList, string tableName, string columnKey, string valueKey, bool searchLike)
        {
            DataSet ds = new DataSet();
            using (SqlConnection sqlConnection = new SqlConnection(CommonSettings.ConnectionString))
            {
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand("LookupTable", sqlConnection);
                sqlCommand.CommandType = CommandType.StoredProcedure;

               
                sqlCommand.Parameters.Add("@_Table", SqlDbType.VarChar).Value = tableName;
                sqlCommand.Parameters.Add("@_ColumnSelect", SqlDbType.VarChar).Value = columnList;
                sqlCommand.Parameters.Add("@_ColumnAlias", SqlDbType.NVarChar).Value = aliasList;
                sqlCommand.Parameters.Add("@_ColumnKey", SqlDbType.VarChar).Value = columnKey;
                sqlCommand.Parameters.Add("@_ValueKey", SqlDbType.VarChar).Value = valueKey;
                sqlCommand.Parameters.Add("@_SearchLike", SqlDbType.Bit).Value = searchLike;

                using (SqlDataAdapter sqlData = new SqlDataAdapter(sqlCommand))
                {
                    sqlData.Fill(ds);
                    ds.Tables[0].TableName = "LookupTable";
                }
                
            }
            return ds;
        }

        public TbLookUpTable LookUpTable_GetList(string columnKey, string tableName)
        {
            var lookUpList = from c in listData.TbLookUpTables
                             where c.ColumnKey == columnKey && c.TableName == tableName
                             select c;

            return lookUpList.ToList().FirstOrDefault();
        }

    }


}
